USE [Weval test]
GO

/****** Object:  StoredProcedure [dbo].[AnswerCount]    Script Date: 8/7/2013 6:49:34 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[AnswerCount] @CRN int, @semester int, @year int


AS
--DECLARE @CRN int = 30626, @semester int = 3, @year int = 2013, @testID int = 64529;

DECLARE @testID int = 
(
	SELECT DISTINCT TOP 1 T.ID
	FROM Tests T
		INNER JOIN SectionTests ST ON T.ID = ST.testID
		INNER JOIN Results R ON ST.deliveryID = R.deliveryID
		INNER JOIN CourseSections CS ON R.sectionID = CS.ID
			AND CS.bannerCRN = @CRN
			AND CS.year = @year
			AND CS.semester = @semester
			AND typeID = 5
);


WITH cteMain AS
(
SELECT 
	(subjectCode + ' ' + courseNumber) AS Course,
	CS.courseNumber,
	CS.bannerCRN AS CRN,
	CONVERT(nvarchar(400),Q.text) AS Question,
	CONVERT(nvarchar(400),D.text) AS Answers,
	Q.ID AS QuestionID,
	Q.sequence,
	studentID
FROM SectionsTaught ST
	INNER JOIN Results R ON R.sectionID = ST.sectionID
		AND (R.deleted = 0 OR R.deleted IS NULL)
	INNER JOIN CourseSections CS ON CS.ID = R.sectionID
		AND CS.bannerCRN = @CRN
		AND CS.year = @year
		AND CS.semester = @semester
	INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
	INNER JOIN Questions Q ON Q.ID = QR.questionID
	INNER JOIN Tests T ON T.ID = Q.testID 
		AND T.typeID = 5 --Course Eval
		AND T.ID = @testID
	INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
	INNER JOIN Distractors D ON D.ID = DR.distractorID
	--INNER JOIN Users U ON U.Id = R.studentID
	--INNER JOIN Deliveries DEL ON DEL.ID = R.deliveryID
), cteAnswerCount as
(
SELECT
	Course,
	CRN,
	Question,
    Answers,
	COUNT(Answers) AS 'Answer Count',
	QuestionID,
	sequence
FROM cteMain
GROUP BY
	Course,
	CRN,
	Question,
	Answers,
	QuestionID,
	sequence
), cteStudentCount as
(
SELECT COUNT(DISTINCT studentID) AS 'Student Count'
FROM cteMain
)
SELECT *
FROM cteAnswerCount CROSS JOIN cteStudentCount
ORDER BY sequence